# 数据库操作
# 导入pymysql库
import pymysql

def create_table():
    # 连接数据库
    # cxn=MySQLdb.Connect(host='localhost',user='root',passwd='',db='samp_db',port=3306)
    db = pymysql.connect(host='localhost', user='root', passwd='admin123', db='runoob', port=3306)
    # 创建游标
    cursor = db.cursor()
    sqlstr = '''
            create table IF NOT EXISTS test1(
            id int PRIMARY KEY AUTO_INCREMENT,
            name VARCHAR(20) NOT NULL,
            sex CHAR(4)
            )
        '''
    cursor.execute(sqlstr)
    result = cursor.fetchone()
    print("create table ", result)
    db.close()

# 插入数据
def insert_table():
    # 连接数据库
    # cxn=MySQLdb.Connect(host='localhost',user='root',passwd='',db='samp_db',port=3306)
    db = pymysql.connect(host='localhost', user='root', passwd='admin123', db='runoob', port=3306)
    # 创建游标
    cursor = db.cursor()
    for i in range(0, 10):
        #sqlstr = '''insert into `test1`(name,sex) values("myname",'sex')'''
        sqlstr='''insert into test1 (name,sex) values('%s','%s')''' % ("name" + str(i), "sex" + str(i))
        print("sqlstr == ", sqlstr)
        try:
            result = cursor.execute(sqlstr)
            print("插入数据结果 ", result)
            db.commit()#提交到数据库执行
        except BaseException as e:
            print("插入数据异常" + e)
            db.rollback()#如果发生错误则回滚
    print("关闭数据库")
    db.close()

# 查数据
def query_table():
    db = pymysql.connect(host='localhost', user='root', passwd='admin123', db='runoob', port=3306)
    cursor = db.cursor()
    sqlstr = '''
    select * from test1
    '''
    cursor.execute(sqlstr)
    # 返回元组
    # 元祖使用（），列表使用[],字典使用{}
    rs = cursor.fetchall()
    print("result ", rs)
    for row in rs:
        print("id = {0} name = {1},sex = {2}".format(row[0], row[1], row[2]))
    cursor.close()
    db.close()

# 删除数据
def del_data():
    db = pymysql.connect(host='localhost', user='root', passwd='admin123', db='runoob', port=3306)
    cursor = db.cursor()
    sql_str = "delete from test1 where name = '11'"
    print("删除语句 ",sql_str)
    try:
        result = cursor.execute(sql_str)
        print("执行结果 ", result)
        db.commit()
    except Exception as e:
        print("异常 "+e)
        db.rollback()
    print("关闭数据库")
    cursor.close()
    db.close()

# 更新数据库
def update_data():
    db = pymysql.connect(host='localhost', user='root', passwd='admin123', db='runoob', port=3306)
    cursor = db.cursor()
    sql_str = "update test1 set name = '%s' where id = %d" % ("update", 5)
    print("更新语句 ", sql_str)
    try:
        result = cursor.execute(sql_str)
        print("执行结果 ", result)
        db.commit()
    except Exception as e:
        print("异常 "+e)
        db.rollback()
    print("关闭数据库")
    cursor.close()
    db.close()



if __name__ == '__main__':
    #create_table()
    #insert_table()
    query_table()
    update_data()
    query_table()
